# app/database/mysql_connector.py
import mysql.connector
from mysql.connector import Error
import logging
import os
from dotenv import load_dotenv  # 导入 load_dotenv

# 加载 .env 文件
load_dotenv()
# 设置日志
logger = logging.getLogger("mysql_connector")
logger.info(f"Connecting to MySQL database... ({os.getenv('DB_HOST')})")
class MySQLConnector:
    def __init__(self):
        try:
            self.connection = mysql.connector.connect(
                host=os.getenv("DB_HOST"),
                database=os.getenv("DB_DATABASE"),
                user=os.getenv("DB_USER"),
                password=os.getenv("DB_PASSWORD")
            )
            if self.connection.is_connected():
                logger.info('Connected to MySQL database')
        except Error as e:
            logger.error(f"Error while connecting to MySQL: {e}")

    def execute(self, sql):
        try:
            cursor = self.connection.cursor()
            cursor.execute(sql)
            self.connection.commit()
            logger.info("SQL executed successfully")
        except Error as e:
            logger.error(f"Error while executing SQL: {e}")
        finally:
            if self.connection.is_connected():
                cursor.close()

    def query(self, sql):
        try:
            cursor = self.connection.cursor(dictionary=True)
            cursor.execute(sql)
            results = cursor.fetchall()
            return results
        except Error as e:
            logger.error(f"Error while querying SQL: {e}")
            return []
        finally:
            if self.connection.is_connected():
                cursor.close()

if __name__ == "__main__":
    MySQLConnector()